﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace JobCreateInDocs2
{
    public static class DBSuplier
    {
        #region Members
        private static string _ConnStr = "";
        public static string ConnStr
        {
            get
            {
                _ConnStr = KoEnvironment.GetAppSetting("AXAPTAConnect");
                return DBSuplier._ConnStr;
            }
        }
        #endregion Members
        #region SQL
        private static string _sql_GetDataByDateTime =
@"
SELECT
    NAMEALIAS AS NAME
    , ACCOUNTNUM AS ID
    , NAME AS FullName
    , VENDACCOUNTCOGNOS
    , MODIFIEDDATE
    , MODIFIEDTIME
FROM
    dbo.v_khim_VENDTABLE AS w
WHERE
    (MODIFIEDDATE >= ?) AND (MODIFIEDTIME >= ?)
    OR (MODIFIEDDATE >= ?)
ORDER BY FullName
"
;
        #endregion SQL
        #region Data
        public static Exception GetDataByDataTime(ref DataTable tblRet, DateTime dtFrom)
        {
            Exception ret = null;
            string connstr = ConnStr;
            if (tblRet == null)
                tblRet = new DataTable();
            DateTime d = new DateTime(dtFrom.Year, dtFrom.Month, dtFrom.Day);
            DateTime d2 = d.AddDays(1);
            TimeSpan t = dtFrom.TimeOfDay;
            int seconds = 0;
            if (t.TotalSeconds > 0)
                seconds = Convert.ToInt32(t.TotalSeconds - 1);
            using (OleDbConnection conn = new OleDbConnection(connstr))
            {
                try
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(_sql_GetDataByDateTime, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandTimeout = 6000;
                        cmd.Parameters.Add("ModifiedDate", DbType.DateTime).Value = d;
                        cmd.Parameters.Add("ModifiedTime", DbType.Int32).Value = seconds;
                        cmd.Parameters.Add("ModifiedDate2", DbType.DateTime).Value = d2;
                        using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                        {
                            da.Fill(tblRet);
                        }
                    }
                }
                catch (Exception ex)
                {
                    ret = new Exception(ex.Message, ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return ret;
        }
        #endregion Data
    }
}
